package com.example.demo.service.impl;

import com.example.demo.mapper.StoreSellPriceMapper;
import com.example.demo.model.entity.StoreSellPrice;
import com.example.demo.service.ExcelService;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author Jamie.王
 * @description
 * @date 2021/11/22 12:04
 */
@Service
@Slf4j
@Transactional
public class ExcelServiceImpl implements ExcelService {

    @Autowired
    private StoreSellPriceMapper storeSellPriceMapper;

    @SneakyThrows
    @Override
    public String importAndParse(MultipartFile file) {
        // 获取文件名
        String fileName = file.getOriginalFilename();
        if (StringUtils.isEmpty(fileName)){
            return "文件不能为空";
        }
        // 获取文件后缀
        String prefix=fileName.substring(fileName.lastIndexOf("."));
        if (!prefix.toLowerCase().contains("xls") && !prefix.toLowerCase().contains("xlsx") ){
            return "文件格式异常，请上传Excel文件格式";
        }
        // 防止生成的临时文件重复-建议使用UUID
        String nowDate = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        final File excelFile = File.createTempFile(nowDate, prefix);
        file.transferTo(excelFile);

        //由于2003和2007的版本所使用的接口不一样，所以这里统一用Workbook做兼容
        boolean isExcel2003 = prefix.toLowerCase().endsWith("xls")?true:false;
        Workbook workbook = null;
        if(isExcel2003){
            workbook = new HSSFWorkbook(new FileInputStream(excelFile));
        }else{
            workbook = new XSSFWorkbook(new FileInputStream(excelFile));
        }
        ArrayList<StoreSellPrice> list = new ArrayList<>();

        Sheet aggSheet = workbook.getSheetAt(0);
        Sheet liyuanSheet = workbook.getSheetAt(2);
        Sheet huangheSheet = workbook.getSheetAt(3);
        Sheet guangqiSheet = workbook.getSheetAt(4);
        Sheet shijihuiSheet = workbook.getSheetAt(5);
        Sheet jingpinSheet = workbook.getSheetAt(6);
        Sheet wandingSheet = workbook.getSheetAt(7);
        Sheet jingweihuiSheet = workbook.getSheetAt(8);
        for (int i = 3; i <= 33; i++) {
            StoreSellPrice liyuanlu = new StoreSellPrice();
            liyuanlu.setStoreId(1L);
            liyuanlu.setStoreName("丽园店");

            StoreSellPrice huanghelu = new StoreSellPrice();
            huanghelu.setStoreId(2L);
            huanghelu.setStoreName("黄河路店");

            StoreSellPrice guangqicheng = new StoreSellPrice();
            guangqicheng.setStoreId(3L);
            guangqicheng.setStoreName("光启城店");

            StoreSellPrice shijihui = new StoreSellPrice();
            shijihui.setStoreId(4L);
            shijihui.setStoreName("世纪汇店");

            StoreSellPrice jingpin = new StoreSellPrice();
            jingpin.setStoreId(5L);
            jingpin.setStoreName("晶品店");

            StoreSellPrice wanding = new StoreSellPrice();
            wanding.setStoreId(6L);
            wanding.setStoreName("畹町路店");

            StoreSellPrice jingweihui = new StoreSellPrice();
            jingweihui.setStoreId(7L);
            jingweihui.setStoreName("经纬汇店");

            //汇总数据
            Row aggRow = aggSheet.getRow(i);
            parseAggData(liyuanlu, huanghelu, guangqicheng, shijihui, jingpin, wanding, jingweihui, aggRow);

            //丽园店
            Row liyuanRow = liyuanSheet.getRow(i);
            parseDetailData(liyuanlu, liyuanRow);

            //黄河路店
            Row huangheRow = huangheSheet.getRow(i);
            parseDetailData(huanghelu, huangheRow);

            //光启城店
            Row guangqiRow = guangqiSheet.getRow(i);
            parseDetailData(guangqicheng, guangqiRow);

            //世纪汇店
            Row shijihuiRow = shijihuiSheet.getRow(i);
            parseDetailData(shijihui, shijihuiRow);

            //晶品店
            Row jingpinRow = jingpinSheet.getRow(i);
            parseDetailData(jingpin, jingpinRow);

            //畹町路店
            Row wanDingRow = wandingSheet.getRow(i);
            parseDetailData(wanding, wanDingRow);

            //经纬汇店
            Row jingweihuiRow = jingweihuiSheet.getRow(i);
            parseDetailData(jingweihui, jingweihuiRow);

            list.add(liyuanlu);
            list.add(huanghelu);
            list.add(guangqicheng);
            list.add(shijihui);
            list.add(jingpin);
            list.add(wanding);
            list.add(jingweihui);
        }
        addCommonParam(list);
        for (StoreSellPrice storeSellPrice : list) {
            storeSellPriceMapper.insert(storeSellPrice);
        }
        return "成功";
    }

    @Override
    public void downResultData(String date, HttpServletResponse response, HttpServletRequest request) throws IOException, ParseException {
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/vnd.ms-excel");
        OutputStream out = response.getOutputStream();
        //attachment表示以附件方式下载。如果要在页面中打开，则改为 inline
        response.setHeader("Content-Disposition", "inline; filename="+date+".xls");
        //创建workbook工作薄
        Workbook workbook = new HSSFWorkbook();
        //创建工作表
        Sheet sheet1 = workbook.createSheet("门店营业额");
        Sheet sheet2 = workbook.createSheet("门店周数据环比");
        Sheet sheet3 = workbook.createSheet("丽园路店业绩分析");
        Sheet sheet4 = workbook.createSheet("黄河路店业绩分析");
        Sheet sheet5 = workbook.createSheet("光启城店业绩分析");
        Sheet sheet6 = workbook.createSheet("世纪汇店业绩分析");
        Sheet sheet7 = workbook.createSheet("晶品店业绩分析");
        Sheet sheet8 = workbook.createSheet("畹町路店业绩分析");
        Sheet sheet9 = workbook.createSheet("经纬汇店业绩分析");
        //设置单元格样式
        HSSFCellStyle hssfCellStyle = (HSSFCellStyle) workbook.createCellStyle();
        hssfCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中显示
        hssfCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//纵向居中
        //计算往前顺移五周的日期
        Map<Integer,List<String>> dateMap = convertDate(date);
        //第一个sheet
        createData(sheet1,hssfCellStyle,dateMap);
        //创建行
        Row row = sheet2.createRow(0);
        //创建单元格
        Cell cell = row.createCell(0);
        //设置第一行第一格的值
        cell.setCellValue("姓名");
        //设置单元格的文本居中显示
        cell.setCellStyle(hssfCellStyle);
        //创建单元格
        Cell cell1 = row.createCell(1);
        //设置第一行第一格的值
        cell1.setCellValue("性别");
        cell1.setCellStyle(hssfCellStyle);
        //创建单元格
        Cell cell2 = row.createCell(2);
        //设置第一行第一格的值
        cell2.setCellValue("年龄");
        cell2.setCellStyle(hssfCellStyle);
        //创建单元格
        Cell cell3 = row.createCell(3);
        //设置第一行第一格的值
        cell3.setCellValue("家庭住址");
        cell3.setCellStyle(hssfCellStyle);
        for (int i = 1; i <= 5; i++) {
            //创建行
            Row rows = sheet3.createRow(i);
            //创建单元格
            Cell cells = rows.createCell(0);
            //设置第一行第一格的值
            cells.setCellValue("张三"+i);
            //创建单元格
            Cell cell1s = rows.createCell(1);
            //设置第一行第一格的值
            cell1s.setCellValue("男");
            //创建单元格
            Cell cell2s = rows.createCell(2);
            //设置第一行第一格的值
            cell2s.setCellValue(18+i);
            //创建单元格
            Cell cell3s = rows.createCell(3);
            //设置第一行第一格的值
            cell3s.setCellValue("家庭住址"+i);
        }
        workbook.write(out);
        System.out.println("数据写入成功！");
        out.flush();
        out.close();
    }

    private Map<Integer, List<String>> convertDate(String yyyyMMdd) throws ParseException {
        Date date = new SimpleDateFormat("yyyyMMdd").parse(yyyyMMdd);
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);

        SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd");
        Map<Integer, List<String>> map = new HashMap<Integer, List<String>>();
        for (int i = 5; i >= 1; i--) {
            ArrayList<String> list = new ArrayList<>();
            calendar.add(Calendar.DAY_OF_MONTH,-1);
            String dateStr0 = format.format(calendar.getTime());
            list.add(dateStr0);
            calendar.add(Calendar.DAY_OF_MONTH,-6);
            String dateStr1 = format.format(calendar.getTime());
            list.add(dateStr1);
            map.put(i,list);
        }
        // 最终的五周日期数据结构
        // {
        //     5:["2021/11/25","2021/11/19"],
        //     4:["2021/11/18","2021/11/12"],
        //     3:["2021/11/11","2021/11/05"],
        //     2:["2021/11/04","2021/10/29"],
        //     1:["2021/10/28","2021/10/22"],
        // }
        return map;
    }

    private void createData(Sheet sheet, HSSFCellStyle style, Map<Integer, List<String>> dateMap) throws ParseException {
        //合并单元格,(起始行,结束行,起始列,结束列)
        CellRangeAddress cell_mendianyingyee = new CellRangeAddress(0, 0, 0, 8);
        CellRangeAddress cell_zhouqi = new CellRangeAddress(1, 3, 0, 0);
        CellRangeAddress cell_beforeTwo = new CellRangeAddress(1, 3, 1, 1);
        CellRangeAddress cell_beforeOne = new CellRangeAddress(1, 3, 2, 2);
        CellRangeAddress cell_current = new CellRangeAddress(1, 3, 3, 3);
        sheet.addMergedRegion(cell_mendianyingyee);
        sheet.addMergedRegion(cell_zhouqi);
        sheet.addMergedRegion(cell_beforeTwo);
        sheet.addMergedRegion(cell_beforeOne);
        sheet.addMergedRegion(cell_current);
        setData(sheet, style, 0, 0,"门店营业额");
        SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd");
        String dateStr = dateMap.get(5).get(0);
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(format.parse(dateStr));
        int currentMonth = calendar.get(Calendar.MONTH)+1;
        calendar.add(Calendar.MONTH,-2);
        int beforeTwoMonth = calendar.get(Calendar.MONTH)+1;
        calendar.add(Calendar.MONTH,1);
        int beforeOneMonth = calendar.get(Calendar.MONTH)+1;
        for (int i = 1; i <= 8; i++) {
            for (int j = 0; j < 9; j++) {
                if (i==1){
                    if (j==0) setData(sheet, style, 1, 0, "周期");
                    if (j==1) setData(sheet, style, 1, 1, beforeTwoMonth+"月");
                    if (j==2) setData(sheet, style, 1, 2, beforeOneMonth+"月");
                    if (j==3) setData(sheet, style, 1, 3, currentMonth+"月");
                    if (j==4) {
                        String content = dateMap.get(1).get(1).replace("/", "月");
                        Row row = sheet.createRow(i);
                        Cell cell = row.createCell(j);
                        cell.setCellStyle(style);
                        cell.setCellValue(content);
                    }
                    if (j==5) {
                        String content = dateMap.get(2).get(1).replace("/", "月");
                        setData(sheet, style, i, j, content);
                    }
                    if (j==6) {
                        String content = dateMap.get(3).get(1).replace("/", "月");
                        setData(sheet, style, i, j, content);
                    }
                    if (j==7) {
                        String content = dateMap.get(4).get(1).replace("/", "月");
                        setData(sheet, style, i, j, content);
                    }
                    if (j==8) {
                        String content = dateMap.get(5).get(1).replace("/", "月");
                        setData(sheet, style, i, j, content);
                    }
                }
                if (i==3){
                    if (j==4) {
                        String content = dateMap.get(1).get(0).replace("/", "月");
                        setData(sheet, style, i, j, content);
                    }
                    if (j==5) {
                        String content = dateMap.get(2).get(0).replace("/", "月");
                        setData(sheet, style, i, j, content);
                    }
                    if (j==6) {
                        String content = dateMap.get(3).get(0).replace("/", "月");
                        setData(sheet, style, i, j, content);
                    }
                    if (j==7) {
                        String content = dateMap.get(4).get(0).replace("/", "月");
                        setData(sheet, style, i, j, content);
                    }
                    if (j==8) {
                        String content = dateMap.get(5).get(0).replace("/", "月");
                        setData(sheet, style, i, j, content);
                    }
                }
            }
        }
    }

    private void setData(Sheet sheet, HSSFCellStyle style, int rowNum, int cellNum, String content) {
        Row row = sheet.createRow(rowNum);
        Cell cell = row.createCell(cellNum);
        cell.setCellStyle(style);
        cell.setCellValue(content);
    }

    private void addCommonParam(ArrayList<StoreSellPrice> list) {
        if (!CollectionUtils.isEmpty(list)){
            String yyyyMMdd = new SimpleDateFormat("yyyyMMdd").format(new Date());
            for (StoreSellPrice vo : list) {
                vo.setSellDate(yyyyMMdd);
                vo.setSellDateYear(Integer.parseInt(yyyyMMdd.substring(0,4)));
                vo.setSellDateMonth(Integer.parseInt(yyyyMMdd.substring(4,6)));
                vo.setSellDateDay(Integer.parseInt(yyyyMMdd.substring(6)));
            }
        }
    }

    private void parseDetailData(StoreSellPrice vo, Row row) {
        vo.setAllCount(new Double(row.getCell(2).getNumericCellValue()).intValue());
        vo.setAllPrice(new BigDecimal(Double.parseDouble(String.valueOf(row.getCell(3).getNumericCellValue()))));
        vo.setMeituanCount(new Double(row.getCell(4).getNumericCellValue()).intValue());
        vo.setMeituanPrice(new BigDecimal(Double.parseDouble(String.valueOf(row.getCell(5).getNumericCellValue()))));
        vo.setElemeCount(new Double(row.getCell(6).getNumericCellValue()).intValue());
        vo.setElemePrice(new BigDecimal(Double.parseDouble(String.valueOf(row.getCell(7).getNumericCellValue()))));
        vo.setCashCount(new Double(row.getCell(8).getNumericCellValue()).intValue());
        vo.setCashPrice(new BigDecimal(Double.parseDouble(String.valueOf(row.getCell(9).getNumericCellValue()))));
        vo.setWxzfbCount(new Double(row.getCell(10).getNumericCellValue()).intValue());
        vo.setWxzfbPrice(new BigDecimal(Double.parseDouble(String.valueOf(row.getCell(11).getNumericCellValue()))));
        vo.setDianpingCount(new Double(row.getCell(12).getNumericCellValue()).intValue());
        vo.setDianpingPrice(new BigDecimal(Double.parseDouble(String.valueOf(row.getCell(13).getNumericCellValue()))));
    }


    private void parseAggData(StoreSellPrice liyuanlu, StoreSellPrice huanghelu, StoreSellPrice guangqicheng,
                              StoreSellPrice shijihui, StoreSellPrice jingpin,
                              StoreSellPrice wanding, StoreSellPrice jingweihui, Row row) {
        liyuanlu.setDinePrice(new BigDecimal(row.getCell(5).getNumericCellValue()));
        liyuanlu.setTakeoutPrice(new BigDecimal(row.getCell(6).getNumericCellValue()));
        liyuanlu.setGroupPrice(new BigDecimal(row.getCell(7).getNumericCellValue()));

        huanghelu.setDinePrice(new BigDecimal(row.getCell(9).getNumericCellValue()));
        huanghelu.setTakeoutPrice(new BigDecimal(row.getCell(10).getNumericCellValue()));
        huanghelu.setGroupPrice(new BigDecimal(row.getCell(11).getNumericCellValue()));

        guangqicheng.setDinePrice(new BigDecimal(row.getCell(13).getNumericCellValue()));
        guangqicheng.setTakeoutPrice(new BigDecimal(row.getCell(14).getNumericCellValue()));
        guangqicheng.setGroupPrice(new BigDecimal(row.getCell(15).getNumericCellValue()));

        shijihui.setDinePrice(new BigDecimal(row.getCell(17).getNumericCellValue()));
        shijihui.setTakeoutPrice(new BigDecimal(row.getCell(18).getNumericCellValue()));
        shijihui.setGroupPrice(new BigDecimal(row.getCell(19).getNumericCellValue()));

        jingpin.setDinePrice(new BigDecimal(row.getCell(21).getNumericCellValue()));
        jingpin.setTakeoutPrice(new BigDecimal(row.getCell(22).getNumericCellValue()));
        jingpin.setGroupPrice(new BigDecimal(row.getCell(23).getNumericCellValue()));

        wanding.setDinePrice(new BigDecimal(row.getCell(25).getNumericCellValue()));
        wanding.setTakeoutPrice(new BigDecimal(row.getCell(26).getNumericCellValue()));
        wanding.setGroupPrice(new BigDecimal(row.getCell(27).getNumericCellValue()));

        jingweihui.setDinePrice(new BigDecimal(row.getCell(29).getNumericCellValue()));
        jingweihui.setTakeoutPrice(new BigDecimal(row.getCell(30).getNumericCellValue()));
        jingweihui.setGroupPrice(new BigDecimal(row.getCell(31).getNumericCellValue()));
    }

    private Integer doubleConvertInteger(double number) {
        BigDecimal bd=new BigDecimal(number).setScale(0, BigDecimal.ROUND_HALF_UP);
        return Integer.parseInt(bd.toString());
    }
}
